package com.loongtech.bi.advertisment.service.impl;

import com.loongtech.bi.advertisment.model.ActiveTranData;
import com.loongtech.bi.advertisment.model.GroupDetailData;
import com.loongtech.bi.advertisment.model.NewRemainData;
import com.loongtech.bi.advertisment.service.AdvDayDataGroupService;
import com.loongtech.bi.dao.CommonDao;
import com.loongtech.bi.entity.pay.EntityNewUserLTV;
import com.loongtech.core.util.Time;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service("advDayDataGroupService")
public class AdvDayDataGroupServiceImpl implements AdvDayDataGroupService {

    @Resource(name = "commonDao")
    private CommonDao commonDao;

    @Override
    public List<ActiveTranData> getActiveTranDatas(String startday, String endday, String[] selectedAffiliation,
                                                   String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype,
                                                   String[] selectedAdvPosition) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dateStr,a.channelName,a.agent,a.clkEquipment, ");
        sql.append("a.actEquipment,a.newActive,a.newEquipment,a.newAccount,a.newPay,a.newPayUser, ");
        sql.append("case when a.newAccount=null then 0 else round(a.newPayUser/a.newAccount,2) end as payrate,round(cost.discount)discount ");
        sql.append(",case when a.newAccount=null then 0 else round(cost.discount/a.newAccount,2) end as cpa,b.totalpay,d.num,round(d.num/a.newAccount,2) as tpr,case when cost.discount=null then 0 else round(b.totalpay*cost.roi/cost.discount,4) * 100 end as roi from (  ");
        sql.append("select dateStr , channelName , agent, ");
        sql.append("sum(clkEquipment)clkEquipment,sum(actEquipment)actEquipment,sum(newActive)newActive ");
        sql.append(",sum(newEquipment)newEquipment,sum(newAccount)newAccount,round(sum(newPay)/100) as newPay,sum(newPayUser)newPayUser from (");
        sql.append("select ifnull(a.logdate,'')  as dateStr, ");
        sql.append("ifnull(bi.channelName,a.url) channelName,ifnull(bi.agent,a.url) agent, ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment, ");
        sql.append("ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, ifnull(newuser,0) newAccount,ifnull(paymoney,0) newPay, ifnull(payuser,0) newPayUser  ");
        sql.append("from td_adv_active a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where  a.logdate >=? and a.logdate <=?  ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(")a group by dateStr , channelName , agent )a  ");
        sql.append("left join bi_advcost cost on cost.ldate = a.dateStr and cost.channelName = a.channelName and cost.agent = a.agent ");
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and cost.platform in (" + getParams(selectedPlatform) + ")");
        }
        sql.append("left join (select ifnull(a.createdate,'')  as dateStr,   ");
        sql.append("ifnull(bi.channelName,a.url) channelName,ifnull(bi.agent,a.url) agent , ifnull(sum(totalpay),0) totalpay   ");
        sql.append("from td_adv_totalpay a  ");
        sql.append("left join bi_advconfig bi on a.url = bi.id  ");
        sql.append("where a.createdate >=? and a.createdate <=?  ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(" group by a.createdate,bi.channelName,bi.agent)b on a.dateStr = b.dateStr  and a.channelName = b.channelName and a.agent = b.agent ");
        sql.append(" left join (select ifnull(a.createdate,'')  as dateStr, ifnull(bi.channelName,a.url) channelName,ifnull(bi.agent,a.url) agent , ifnull(sum(num),0) num   from td_adv_totalpayuser a  left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=?  ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and bi.affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and bi.channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and bi.agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and bi.platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and bi.paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and bi.advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(" group by a.createdate,bi.channelName,bi.agent)d  on a.dateStr = d.dateStr  and a.channelName = d.channelName and a.agent = d.agent ");
        sql.append("  order by dateStr desc , clkEquipment desc ");
        return commonDao.queryObjList(sql.toString(), new Object[]{startday, endday, startday, endday, startday, endday},
                ActiveTranData.class);
    }

    @Override
    public List<NewRemainData> getNewRemainDatas(String startday, String endday, String[] selectedAffiliation,
                                                 String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype,
                                                 String[] selectedAdvPosition) throws Exception {

        StringBuilder sql = new StringBuilder();
        sql.append("select  ifnull(createdate,'') dateStr, channelName, agent,ifnull(max(newuser),0) newAccount  ");
        sql.append(getSelRemainParams(90, "remainnum", "remain"));
        sql.append("from ( ");
        sql.append("select a.logdate, a.createdate , a.channelName, a.agent,remainnum,newuser from (  ");
        sql.append("select t.logdate, createdate , channelName, agent , sum(t.remainnum)remainnum   ");
        sql.append("from td_adv_newremain t    ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id   ");
        sql.append("where t.createdate >= ? and t.createdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate,createdate , channelName, agent)a inner join ( ");
        sql.append("select t.logdate,channelName, agent,sum(newuser)newuser ");
        sql.append("from td_adv_active t   ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id  ");
        sql.append("where t.logdate >= ? and t.logdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate,channelName, agent)b ");
        sql.append("on a.channelName = b.channelName and a.agent = b.agent and a.createdate = b.logdate)as a  ");
        sql.append("group by createdate , channelName, agent ");
        return commonDao.queryObjList(sql.toString(), new Object[]{startday, endday, startday, endday},
                NewRemainData.class);
    }

    @Override
    public List<List<String>> getTotalPayDatas(String startday, String endday, String[] selectedAffiliation,
                                               String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype,
                                               String[] selectedAdvPosition) throws Exception {

        StringBuilder sql = new StringBuilder();
        sql.append("select  createdate ,max(1),max(2), channelName, agent,ifnull(max(newuser),0) newuser  ");
        sql.append(getSelPayParams(90, "totalpay", "income"));
        sql.append("from(  ");
        sql.append("select a.logdate, a.createdate , a.channelName, a.agent,totalpay,newuser from (  ");
        sql.append("select t.logdate, createdate , channelName, agent , sum(t.totalpay)totalpay   ");
        sql.append("from td_adv_totalpay t    ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id   ");
        sql.append("where t.createdate >= ? and t.createdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate,createdate , channelName, agent)a left join ( ");
        sql.append("select t.logdate,channelName, agent,sum(newuser)newuser ");
        sql.append("from td_adv_active t   ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id  ");
        sql.append("where t.logdate >= ? and t.logdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate,channelName, agent)b ");
        sql.append("on a.channelName = b.channelName and a.agent = b.agent and a.createdate = b.logdate)as a ");
        sql.append("group by createdate , channelName, agent order by createdate desc ");

        List<List<String>> re = commonDao.queryListData(sql.toString(), new Object[]{startday, endday, startday,
                endday});

		for (List<String> ls : re) {
			for (int i = 7; i < ls.size(); i++) {
				if (!"-1".equals(ls.get(i))) {
                    ls.set(i, String.valueOf(Integer.valueOf(ls.get(i)) + Integer.valueOf(ls.get(i - 1))));
                }
			}
		}
        return re;
    }

    private String getSelRemainParams(int days, String name, String paramName) throws Exception {
        StringBuilder sb = new StringBuilder();
        for (int i = 1; i < days; i++) {
            sb.append(",if(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i
                    + " day)>=curdate(),'-',sum(if(logdate=DATE_FORMAT(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i + " day),'%Y-%m-%d'), round(" + name + "/newuser,4),''))+'') " + paramName + (i + 1) + "  ");
        }
        return sb.toString();
    }

    private String getSelPayParams(int days, String name, String paramName) throws Exception {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < days; i++) {
            sb.append(",if(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i
                    + " day)>=curdate(),-1,sum(if(logdate=DATE_FORMAT(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i + " day),'%Y-%m-%d'), " + name + ",0))) " + paramName + (i + 1) + "   ");
        }
        return sb.toString();
    }

    private String getParams(String[] param) throws Exception {
        StringBuilder s = new StringBuilder();
        for (String str : param) {
            s.append("'" + str + "',");
        }
        return s.substring(0, s.length() - 1);
    }

    @Override
    public int getAllRoi(String startday, String endday, String[] selectedAffiliation, String[] selectedChannelName,
                         String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype, String[] selectedAdvPosition)
            throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select ifnull(sum(round(totalpay*   ");
        sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi   ");
        sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi   ");
        sql.append("else bi.roi end,2)),0)  roi from (   ");
        sql.append("select ifnull(a.createdate,'')  as dateStr,   ");
        sql.append("ifnull(bi.channelName,a.url) channelName,ifnull(bi.agent,a.url) agent ,ifnull(bi.platform,a.url) platform , ifnull(sum(totalpay),0) totalpay   ");
        sql.append("from td_adv_totalpay a   ");
        sql.append("left join bi_advconfig bi on a.url = bi.id     ");
        sql.append("where a.createdate >=? and a.createdate <=?   ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(" group by a.createdate,bi.channelName,bi.agent,bi.platform)a    ");
        sql.append(" left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channelName = bi.channelName  and a.dateStr = bi.ldate  ");
        sql.append(" left join bi_advdefault def on 1=1  ");
        return commonDao.queryForInt(sql.toString(), new Object[]{startday, endday});
    }

    @Override
    public List<GroupDetailData> getChannelDetailDatas(String startDay, String endDay) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.channel,a.clkEquipment, ");
        sql.append("a.actEquipment,a.newActive,a.newEquipment,a.newAccount,a.newPay,a.newPayUser, ");
        sql.append("case when a.newAccount=null then 0 else round(a.newPayUser/a.newAccount,2) end as payrate,round(cost.discount) discount ");
        sql.append(",case when a.newAccount=null then 0 else round(cost.discount/a.newAccount,2) end as cpa,b.totalpay, d.num,round(d.num/a.newAccount,2) as tpr,");
        sql.append("case when cost.discount=null then 0 else round(b.roi/cost.discount* 100 ,2) end as roi , ");
        sql.append("case when a.newPayUser=null or a.newPayUser=0 then 0 else  b.totalpay/a.newAccount end as ltv  from ( ");
        sql.append("select channel, ");
        sql.append("sum(clkEquipment)clkEquipment,sum(actEquipment)actEquipment,sum(newActive)newActive ");
        sql.append(",sum(newEquipment)newEquipment,sum(newAccount)newAccount,round(sum(newPay)/100) as newPay,sum(newPayUser)newPayUser from ( ");
        sql.append("select bi.channelName channel,bi.agent agent,  ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment,ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, ");
        sql.append("ifnull(newuser,0) newAccount,ifnull(paymoney,0) newPay, ifnull(payuser,0) newPayUser ");
        sql.append("from td_adv_active a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.logdate >=? and a.logdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append(")a group by channel ");
        sql.append(")a ");
        sql.append("left join ( ");
        sql.append("select c.channelName channel, sum(discount) discount from bi_advcost t ");
        sql.append("left join (select actName,affiliation,agent,channelName,paytype, platform from bi_advconfig group by actName,affiliation,agent,channelName,paytype, platform) c on t.platform = c.platform and t.agent = c.agent and t.channelName = c.channelName ");
        sql.append("where t.ldate >=? and t.ldate <=? ");
        sql.append("group by c.channelName ");
        sql.append(") cost on a.channel = cost.channel ");
        sql.append("left join ( ");
        sql.append("select  a.channel, ");
        sql.append("sum(totalpay) totalpay,sum(round(totalpay* ");
        sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi ");
        sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi ");
        sql.append("else bi.roi end,2))  roi from ( ");
        sql.append("select ifnull(a.createdate,'')  as dateStr, ");
        sql.append("bi.channelName channel,bi.agent agent, ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(sum(totalpay),0) totalpay ");
        sql.append("from td_adv_totalpay a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by a.createdate,bi.channelName,bi.agent,bi.platform)a ");
        sql.append("left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channel = bi.channelName  and a.dateStr = bi.ldate ");
        sql.append("left join bi_advdefault def on 1=1 ");
        sql.append("group by a.channel ");
        sql.append(")b on a.channel = b.channel ");
        sql.append("left join ( ");
        sql.append("select bi.channelName channel,");
        sql.append("ifnull(sum(num),0) num ");
        sql.append("from td_adv_totalpayuser a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by bi.channelName ");
        sql.append(")d on a.channel = d.channel ");
        return commonDao.queryObjList(sql.toString(), new Object[]{startDay, endDay, startDay, endDay, startDay,
                endDay, startDay, endDay}, GroupDetailData.class);
    }

    @Override
    public List<GroupDetailData> getActivityDetailDatas(String startDay, String endDay) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.activity,a.clkEquipment, ");
        sql.append("a.actEquipment,a.newActive,a.newEquipment,a.newAccount,a.newPay,a.newPayUser, ");
        sql.append("case when a.newAccount=null then 0 else round(a.newPayUser/a.newAccount,2) end as payrate,round(cost.discount) discount ");
        sql.append(",case when a.newAccount=null then 0 else round(cost.discount/a.newAccount,2) end as cpa,b.totalpay, d.num,round(d.num/a.newAccount,2) as tpr,");
        sql.append("case when cost.discount=null then 0 else round(b.roi/cost.discount* 100 ,2) end as roi, ");
        sql.append("case when a.newPayUser=null or a.newPayUser=0 then 0 else  b.totalpay/a.newAccount end as ltv  from ( ");
        sql.append("select activity, ");
        sql.append("sum(clkEquipment)clkEquipment,sum(actEquipment)actEquipment,sum(newActive)newActive ");
        sql.append(",sum(newEquipment)newEquipment,sum(newAccount)newAccount,round(sum(newPay)/100) as newPay,sum(newPayUser)newPayUser from ( ");
        sql.append("select bi.channelName channel,bi.agent agent,  ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment,ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, ");
        sql.append("ifnull(newuser,0) newAccount,ifnull(paymoney,0) newPay, ifnull(payuser,0) newPayUser ");
        sql.append("from td_adv_active a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.logdate >=? and a.logdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append(")a group by activity ");
        sql.append(")a ");
        sql.append("left join ( ");
        sql.append("select c.actName activity, sum(discount) discount from bi_advcost t ");
        sql.append("left join (select actName,affiliation,agent,channelName,paytype, platform from bi_advconfig group by actName,affiliation,agent,channelName,paytype, platform) c on t.platform = c.platform and t.agent = c.agent and t.channelName = c.channelName ");
        sql.append("where t.ldate >=? and t.ldate <=? ");
        sql.append("group by c.actName ");
        sql.append(") cost on a.activity = cost.activity ");
        sql.append("left join ( ");
        sql.append("select a.activity, ");
        sql.append("sum(totalpay) totalpay,sum(round(totalpay* ");
        sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi ");
        sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi ");
        sql.append("else bi.roi end,2))  roi from ( ");
        sql.append("select ifnull(a.createdate,'')  as dateStr, ");
        sql.append("bi.channelName channelName,bi.agent agent, ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(sum(totalpay),0) totalpay ");
        sql.append("from td_adv_totalpay a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by a.createdate,bi.channelName,bi.agent,bi.platform)a ");
        sql.append("left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channelName = bi.channelName  and a.dateStr = bi.ldate ");
        sql.append("left join bi_advdefault def on 1=1 ");
        sql.append("group by a.activity ");
        sql.append(")b on a.activity = b.activity   ");
        sql.append("left join ( ");
        sql.append("select bi.actName activity,");
        sql.append("ifnull(sum(num),0) num ");
        sql.append("from td_adv_totalpayuser a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by bi.actName ");
        sql.append(")d on a.activity = d.activity ");
        return commonDao.queryObjList(sql.toString(), new Object[]{startDay, endDay, startDay, endDay, startDay,
                endDay, startDay, endDay}, GroupDetailData.class);
    }

    @Override
    public List<GroupDetailData> getPlatformDetailDatas(String startDay, String endDay) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.platform,a.clkEquipment, ");
        sql.append("a.actEquipment,a.newActive,a.newEquipment,a.newAccount,a.newPay,a.newPayUser, ");
        sql.append("case when a.newAccount=null then 0 else round(a.newPayUser/a.newAccount,2) end as payrate,round(cost.discount) discount ");
        sql.append(",case when a.newAccount=null then 0 else round(cost.discount/a.newAccount,2) end as cpa,b.totalpay, d.num,round(d.num/a.newAccount,2) as tpr,");
        sql.append("case when cost.discount=null then 0 else round(b.roi/cost.discount* 100 ,2) end as roi, ");
        sql.append("case when a.newPayUser=null or a.newPayUser=0 then 0 else  b.totalpay/a.newAccount end as ltv  from ( ");
        sql.append("select platform, ");
        sql.append("sum(clkEquipment)clkEquipment,sum(actEquipment)actEquipment,sum(newActive)newActive ");
        sql.append(",sum(newEquipment)newEquipment,sum(newAccount)newAccount,round(sum(newPay)/100) as newPay,sum(newPayUser)newPayUser from ( ");
        sql.append("select bi.channelName channel,bi.agent agent,  ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment,ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, ");
        sql.append("ifnull(newuser,0) newAccount,ifnull(paymoney,0) newPay, ifnull(payuser,0) newPayUser ");
        sql.append("from td_adv_active a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.logdate >=? and a.logdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append(")a group by platform ");
        sql.append(")a ");
        sql.append("left join ( ");
        sql.append("select c.platform platform, sum(discount) discount from bi_advcost t ");
        sql.append("left join (select actName,affiliation,agent,channelName,paytype, platform from bi_advconfig group by actName,affiliation,agent,channelName,paytype, platform) c on t.platform = c.platform and t.agent = c.agent and t.channelName = c.channelName ");
        sql.append("where t.ldate >=? and t.ldate <=? ");
        sql.append("group by c.platform ");
        sql.append(") cost on a.platform = cost.platform ");
        sql.append("left join ( ");
        sql.append("select  a.platform, ");
        sql.append("sum(totalpay) totalpay,sum(round(totalpay* ");
        sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi ");
        sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi ");
        sql.append("else bi.roi end,2))  roi from ( ");
        sql.append("select ifnull(a.createdate,'')  as dateStr, ");
        sql.append("bi.channelName channel,bi.agent agent, ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(sum(totalpay),0) totalpay ");
        sql.append("from td_adv_totalpay a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by a.createdate,bi.channelName,bi.agent,bi.platform)a ");
        sql.append("left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channel = bi.channelName  and a.dateStr = bi.ldate ");
        sql.append("left join bi_advdefault def on 1=1 ");
        sql.append("group by a.platform ");
        sql.append(")b on a.platform = b.platform   ");
        sql.append("left join ( ");
        sql.append("select bi.platform,");
        sql.append("ifnull(sum(num),0) num ");
        sql.append("from td_adv_totalpayuser a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by bi.platform ");
        sql.append(")d on a.platform = d.platform ");
        return commonDao.queryObjList(sql.toString(), new Object[]{startDay, endDay, startDay, endDay, startDay,
                endDay, startDay, endDay}, GroupDetailData.class);
    }

    @Override
    public List<GroupDetailData> getAgentDetailDatas(String startDay, String endDay) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.agent,a.clkEquipment, ");
        sql.append("a.actEquipment,a.newActive,a.newEquipment,a.newAccount,a.newPay,a.newPayUser, ");
        sql.append("case when a.newAccount=null then 0 else round(a.newPayUser/a.newAccount,2) end as payrate,round(cost.discount) discount ");
        sql.append(",case when a.newAccount=null then 0 else round(cost.discount/a.newAccount,2) end as cpa,b.totalpay, d.num,round(d.num/a.newAccount,2) as tpr,");
        sql.append("case when cost.discount=null then 0 else round(b.roi/cost.discount* 100 ,2) end as roi , ");
        sql.append("case when a.newPayUser=null or a.newPayUser=0 then 0 else  b.totalpay/a.newAccount end as ltv  from ( ");
        sql.append("select agent, ");
        sql.append("sum(clkEquipment)clkEquipment,sum(actEquipment)actEquipment,sum(newActive)newActive ");
        sql.append(",sum(newEquipment)newEquipment,sum(newAccount)newAccount,round(sum(newPay)/100) as newPay,sum(newPayUser)newPayUser from ( ");
        sql.append("select bi.channelName channel,bi.agent agent,  ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment,ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, ");
        sql.append("ifnull(newuser,0) newAccount,ifnull(paymoney,0) newPay, ifnull(payuser,0) newPayUser ");
        sql.append("from td_adv_active a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.logdate >=? and a.logdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append(")a group by agent ");
        sql.append(")a ");
        sql.append("left join ( ");
        sql.append("select c.agent agent, sum(discount) discount from bi_advcost t ");
        sql.append("left join (select actName,affiliation,agent,channelName,paytype, platform from bi_advconfig group by actName,affiliation,agent,channelName,paytype, platform) c on t.platform = c.platform and t.agent = c.agent and t.channelName = c.channelName ");
        sql.append("where t.ldate >=? and t.ldate <=? ");
        sql.append("group by c.agent ");
        sql.append(") cost on a.agent = cost.agent ");
        sql.append("left join ( ");
        sql.append("select  a.agent, ");
        sql.append("sum(totalpay) totalpay,sum(round(totalpay* ");
        sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi ");
        sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi ");
        sql.append("else bi.roi end,2))  roi from ( ");
        sql.append("select ifnull(a.createdate,'')  as dateStr, ");
        sql.append("bi.channelName channel,bi.agent agent, ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(sum(totalpay),0) totalpay ");
        sql.append("from td_adv_totalpay a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by a.createdate,bi.channelName,bi.agent,bi.platform)a ");
        sql.append("left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channel = bi.channelName  and a.dateStr = bi.ldate ");
        sql.append("left join bi_advdefault def on 1=1 ");
        sql.append("group by a.agent ");
        sql.append(")b on a.agent = b.agent   ");
        sql.append("left join ( ");
        sql.append("select bi.agent,");
        sql.append("ifnull(sum(num),0) num ");
        sql.append("from td_adv_totalpayuser a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by bi.agent ");
        sql.append(")d on a.agent = d.agent ");
        return commonDao.queryObjList(sql.toString(), new Object[]{startDay, endDay, startDay, endDay, startDay,
                endDay, startDay, endDay}, GroupDetailData.class);

    }

    @Override
    public List<GroupDetailData> getPaytypeDetailDatas(String startDay, String endDay) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.paytype,a.clkEquipment, ");
        sql.append("a.actEquipment,a.newActive,a.newEquipment,a.newAccount,a.newPay,a.newPayUser, ");
        sql.append("case when a.newAccount=null then 0 else round(a.newPayUser/a.newAccount,2) end as payrate,round(cost.discount) discount ");
        sql.append(",case when a.newAccount=null then 0 else round(cost.discount/a.newAccount,2) end as cpa,b.totalpay, d.num,round(d.num/a.newAccount,2) as tpr,");
        sql.append("case when cost.discount=null then 0 else round(b.roi/cost.discount* 100 ,2) end as roi , ");
        sql.append("case when a.newPayUser=null or a.newPayUser=0 then 0 else  b.totalpay/a.newAccount end as ltv  from ( ");
        sql.append("select paytype, ");
        sql.append("sum(clkEquipment)clkEquipment,sum(actEquipment)actEquipment,sum(newActive)newActive ");
        sql.append(",sum(newEquipment)newEquipment,sum(newAccount)newAccount,round(sum(newPay)/100) as newPay,sum(newPayUser)newPayUser from ( ");
        sql.append("select bi.channelName channel,bi.agent agent,  ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment,ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, ");
        sql.append("ifnull(newuser,0) newAccount,ifnull(paymoney,0) newPay, ifnull(payuser,0) newPayUser ");
        sql.append("from td_adv_active a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.logdate >=? and a.logdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append(")a group by paytype ");
        sql.append(")a ");
        sql.append("left join ( ");
        sql.append("select c.paytype paytype, sum(discount) discount from bi_advcost t ");
        sql.append("left join (select actName,affiliation,agent,channelName,paytype, platform from bi_advconfig group by actName,affiliation,agent,channelName,paytype, platform) c on t.platform = c.platform and t.agent = c.agent and t.channelName = c.channelName ");
        sql.append("where t.ldate >=? and t.ldate <=? ");
        sql.append("group by c.paytype ");
        sql.append(") cost on a.paytype = cost.paytype ");
        sql.append("left join ( ");
        sql.append("select  a.paytype, ");
        sql.append("sum(totalpay) totalpay,sum(round(totalpay* ");
        sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi ");
        sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi ");
        sql.append("else bi.roi end,2))  roi from ( ");
        sql.append("select ifnull(a.createdate,'')  as dateStr, ");
        sql.append("bi.channelName channel,bi.agent agent, ");
        sql.append("bi.actName activity ,  bi.paytype paytype , bi.platform platform, ");
        sql.append("ifnull(sum(totalpay),0) totalpay ");
        sql.append("from td_adv_totalpay a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by a.createdate,bi.channelName,bi.agent,bi.platform)a ");
        sql.append("left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channel = bi.channelName  and a.dateStr = bi.ldate ");
        sql.append("left join bi_advdefault def on 1=1 ");
        sql.append("group by a.paytype ");
        sql.append(")b on a.paytype = b.paytype   ");
        sql.append("left join ( ");
        sql.append("select bi.paytype,");
        sql.append("ifnull(sum(num),0) num ");
        sql.append("from td_adv_totalpayuser a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=? ");
        sql.append("and bi.channelName  is not null and bi.agent is not null and bi.actName is not null ");
        sql.append("and bi.advPosition is not null and bi.paytype is not null and bi.platform is not null ");
        sql.append("group by bi.paytype ");
        sql.append(")d on a.paytype = d.paytype ");
        return commonDao.queryObjList(sql.toString(), new Object[]{startDay, endDay, startDay, endDay, startDay,
                endDay, startDay, endDay}, GroupDetailData.class);
    }

    @Override
    public List<EntityNewUserLTV> getLTVPay(String startday, String endday) throws Exception {
        return commonDao.queryObjList("select * from td_adv_newuserltv where 1=1 and startday>=? and startday<=? and endday<=? order by startday", new Object[] { startday, endday, Time.formatTime(Time.formatDateStr(endday, Time.TIMEPATTERN_SIMPLE) + 119 * Time.DAY,Time.TIMEPATTERN_SIMPLE)}, EntityNewUserLTV.class);
    }
}
